package com.example.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.io.*;
import java.util.*;

/**
 * excel文件相关操作
 */
public class ExcelReader {

    /**
     * 读取excel表格
     * @param file 需要读取的excel表格
     * @return map 返回map类型数据集
     * @throws IOException
     */
    public static List<Map<String, Object>> readExcel(File file) throws IOException {
        List<Map<String, Object>> data = new ArrayList<>();
        try (FileInputStream fis = new FileInputStream(file);
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0);
            // 获取表头，作为键
            Row headerRow = sheet.getRow(0);
            List<String> headers = new ArrayList<>();
            for (Cell cell : headerRow) {
                headers.add(cell.getStringCellValue());
            }

            // 从第二行开始读取数据
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                Map<String, Object> rowData = new HashMap<>();
                for (int j = 0; j < headers.size(); j++) {
                    Cell cell = row.getCell(j);
                    if (cell != null) {
                        String cellTypeName = cell.getCellType().name();
                        switch (cellTypeName) {
                            case "STRING":
                                rowData.put(headers.get(j), cell.getStringCellValue());
                                break;
                            case "NUMERIC":
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    rowData.put(headers.get(j), cell.getDateCellValue());
                                } else {
                                    rowData.put(headers.get(j), cell.getNumericCellValue());
                                }
                                break;
                            case "BOOLEAN":
                                rowData.put(headers.get(j), cell.getBooleanCellValue());
                                break;
                            case "FORMULA":
                                rowData.put(headers.get(j), cell.getCellFormula());
                                break;
                            default:
                                rowData.put(headers.get(j), null);
                        }
                    } else {
                        rowData.put(headers.get(j), null);
                    }
                }
                data.add(rowData);
            }
        }
        return data;
    }

    // 导出 excel 文件
    public static String exportExcel(String dataFilePath, String excelFilePath) throws IOException {
        // 读取 content.txt 文件内容
        List<Map<String, String>> data = readDataFromFile(dataFilePath);

        // 创建 Excel 工作簿和工作表
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Data");

        // 写入表头和数据
        writeDataToSheet(sheet, data);

        // 保存 Excel 文件
        try (FileOutputStream fileOut = new FileOutputStream(excelFilePath)) {
            workbook.write(fileOut);
        }

        // 关闭工作簿
        workbook.close();

        // 返回 excel 文件地址
        return excelFilePath;
    }

    // 从文件中读取数据并解析为键值对的列表
    public static List<Map<String, String>> readDataFromFile(String filePath) throws IOException {
        List<Map<String, String>> data = new ArrayList<>();
        try (BufferedReader br = new BufferedReader(new FileReader(filePath))) {
            String line;
            while ((line = br.readLine()) != null) {
                Map<String, String> rowData = new LinkedHashMap<>();
                String[] pairs = line.split("\t");
                for (String pair : pairs) {
                    String[] keyValue = pair.split(": ");
                    if (keyValue.length == 2) {
                        rowData.put(keyValue[0], keyValue[1]);
                    }
                }
                data.add(rowData);
            }
        }
        return data;
    }

    // 将数据写入工作表，将键作为表头，值作为内容
    private static void writeDataToSheet(Sheet sheet, List<Map<String, String>> data) {
        if (data.isEmpty()) {
            return;
        }

        // 获取所有可能的表头
        Set<String> headers = new LinkedHashSet<>();
        for (Map<String, String> row : data) {
            headers.addAll(row.keySet());
        }
        List<String> headerList = new ArrayList<>(headers);

        // 写入表头
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headerList.size(); i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headerList.get(i));
        }

        // 写入数据
        for (int i = 0; i < data.size(); i++) {
            Map<String, String> rowData = data.get(i);
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < headerList.size(); j++) {
                String header = headerList.get(j);
                Cell cell = row.createCell(j);
                String value = rowData.get(header);
                if (value != null) {
                    cell.setCellValue(value);
                }
            }
        }
    }


    public static void main(String[] args) {
        try {
            File file = new File("input.xlsx");
            List<Map<String, Object>> result = readExcel(file);
            for (Map<String, Object> row : result) {
                System.out.println(row);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}